﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data.SqlServerCe;
using System.Data;
using OverStore.Configuration;
using OverStore.Runtime.Logger;
using OverStore.Runtime.Storage;
using OverStore.Runtime;
using OverStore.Sql;

namespace OverStore.TutorialAsTest.UnitTest.Components.AdoNetWrapper
{
    [TestClass]
    public class AdoNetCommand_Test
    {
        private readonly List<string> ItemNames =
            new List<string>() { "Item", "Row", "Column", "StoredProcedure" };

        private AdoNetStorage<SqlCeConnection> CreateDataSource(string connectionString)
        {
            return new AdoNetStorage<SqlCeConnection>(connectionString, ProcessOpenedReadersMode.ThrowException, new DebugLogger(), new SqlServerCeFormatting(), new OverStore.Scoping.Manager.OperationScopeManager());
        }

        [TestMethod]
        public void AdoNetCommand_ExecuteReader()
        {
            var connectionString = TestHelper.CreateTestDbCopyAndBuildConnectionString();
            var source = CreateDataSource(connectionString);
            var command = new AdoNetCommand("SELECT * FROM AdoNetCommandTestTable",
                CommandType.Text,
                new AdoNetParameter[] { },
                AdoCommandResultType.DataReaderResultSet, DummyLogger.Value);

            int rowsFound = 0;
            foreach (var record in source.ReadAll(command, EmptyTypedRecord.Value))
            {
                rowsFound++;
                string name = record.GetValue("Name").ToString();
                Assert.IsTrue(ItemNames.Contains(name));
            }

            Assert.AreEqual(4, rowsFound);
        }

        [TestMethod]
        public void AdoNetCommand_ExecuteReader_Parameters()
        {
            var connectionString = TestHelper.CreateTestDbCopyAndBuildConnectionString();
            var source = CreateDataSource(connectionString);
            var command = new AdoNetCommand(
                "SELECT * FROM AdoNetCommandTestTable WHERE IsActive = @IsActive",
                CommandType.Text,
                new[] { new AdoNetParameter("IsActive", false, DbType.Boolean) },
                AdoCommandResultType.DataReaderResultSet, DummyLogger.Value);

            var parameters = new DictionaryTypedRecord(new KeyValuePair<string, object>("isactive", false));

            int rowsFound = 0;
            foreach (var record in source.ReadAll(command, parameters))
            {
                rowsFound++;
                string name = record.GetValue("Name").ToString();
                Assert.IsTrue(ItemNames.Contains(name));
            }
            Assert.AreEqual(1, rowsFound);
        }

        [TestMethod]
        public void AdoNetCommand_ExecuteRead()
        {
            var connectionString = TestHelper.CreateTestDbCopyAndBuildConnectionString();
            var source = CreateDataSource(connectionString);
            var command = new AdoNetCommand(
                "SELECT * FROM AdoNetCommandTestTable WHERE ID = @ID",
                CommandType.Text,
                new[] { new AdoNetParameter("ID", false, DbType.Int32) },
                AdoCommandResultType.DataReaderResultSet, DummyLogger.Value);

            var parameters = new DictionaryTypedRecord(new KeyValuePair<string, object>("id", 3));

            int rowsAffected;
            var result = source.Execute(command, parameters, out rowsAffected);
            Assert.AreEqual(1, rowsAffected);

            Assert.AreEqual(3, result.GetValue("id"));
            Assert.AreEqual("Column", result.GetValue("Name"));
            Assert.AreEqual(true, result.GetValue("isActive"));
        }

        [TestMethod]
        public void AdoNetCommand_ExecuteWrite()
        {
            var connectionString = TestHelper.CreateTestDbCopyAndBuildConnectionString();
			using (var source = CreateDataSource(connectionString))
			{
				using (var scope = source.OpenScope())
				{
					var command = new AdoNetCommand(
						"INSERT INTO AdoNetCommandTestTable (Name, IsActive) VALUES (@Name, @IsActive) ",
						CommandType.Text,
						new[] {new AdoNetParameter("Name", false, DbType.String), new AdoNetParameter("IsActive", false, DbType.Boolean)},
						AdoCommandResultType.OutputParameters,
						new DebugLogger());

					var parameters = new DictionaryTypedRecord(new KeyValuePair<string, object>("Name", "Function"), new KeyValuePair<string, object>("IsActive", false));

					int rowsAffected;
					var result = source.Execute(command, parameters, out rowsAffected);
					Assert.AreEqual(1, rowsAffected);
					scope.Complete();
				}

				using (var scope = source.OpenScope())
				{
					var deleteCommand = new AdoNetCommand(
						"DELETE FROM AdoNetCommandTestTable WHERE Name = 'Function'",
						CommandType.Text,
						new AdoNetParameter[] {},
						AdoCommandResultType.OutputParameters,
						new DebugLogger());

					int deletedRows;
					var deleteResult = source.Execute(deleteCommand, EmptyTypedRecord.Value, out deletedRows);
					Assert.AreEqual(1, deletedRows);
					scope.Complete();
				}
			}
        }

        [TestMethod]
        [ExpectedException(typeof(PersistingRuntimeException))]
        public void AdoNetCommand_MissingParameter()
        {
            var connectionString = TestHelper.CreateTestDbCopyAndBuildConnectionString();
            var source = CreateDataSource(connectionString);
            var command = new AdoNetCommand(
                "INSERT INTO AdoNetCommandTestTable (Name, IsActive) VALUES (@Name, @IsActive) ",
                CommandType.Text,
                new[] { new AdoNetParameter("Name", false, DbType.String), new AdoNetParameter("IsActive", false, DbType.Boolean) },
                AdoCommandResultType.OutputParameters, DummyLogger.Value);

            var parameters = new DictionaryTypedRecord(new KeyValuePair<string, object>("Name", "Function"));

            int rowsAffected;
            source.Execute(command, parameters, out rowsAffected);
        }
    }
}
